open_files_limit 是mysql中的一个全局变量且不可动态修改。它控制着mysqld进程能使用的最大文件描述(FD)符数量。需要注意的是这个变量的值并不一定是你设定的值，mysqld会在系统允许的情况下尽量获取更多的FD数量。
首先我们看下官方文档的定义：
The number of files that the operating system permits mysqld to open. The value of this variable at
runtime is the real value permitted by the system and might be different from the value you specify at
server startup. The value is 0 on systems where MySQL cannot change the number of open files.
The effective open_files_limit value is based on the value specified at system startup (if any) and
the values of max_connections and table_open_cache, using these formulas:
1)10 + max_connections + (table_open_cache * 2)
2)max_connections * 5
3)open_files_limit value specified at startup, 5000 if none
mysqld在启动的时候，根据上述三种算法对该变量进行调整，并取三种算法中的最大值。也就是说最终确定的open_files_limit 可能比你设定的大，也可能小。
下面我们再瞅一眼源码(mysqld.cc)：
/**
Adjust @c open_files_limit.
Computation is based on:
- @c max_connections,
- @c table_cache_size,
- the platform max open file limit.
*/
void adjust_open_files_limit(ulong *requested_open_files)
{
ulong limit_1;
ulong limit_2;
ulong limit_3;
ulong request_open_files;
ulong effective_open_files;
/* MyISAM requires two file handles per table. */
limit_1= 10 + max_connections + table_cache_size * 2;
/*
We are trying to allocate no less than max_connections*5 file
handles (i.e. we are trying to set the limit so that they will
be available).
*/
limit_2= max_connections * 5;
/* Try to allocate no less than 5000 by default. */
limit_3= open_files_limit ? open_files_limit : 5000;
request_open_files= max<ulong>(max<ulong>(limit_1, limit_2), limit_3);
/* Notice: my_set_max_open_files() may return more than requested. */
effective_open_files= my_set_max_open_files(request_open_files);
//最终确定的值可能会比你设定的值大，也可能小。 如果小的话下面这段代码是打出两个警告信息。大的话当然没关系啦。
if (effective_open_files < request_open_files)
{
if (open_files_limit == 0)
{
sql_print_warning("Changed limits: max_open_files: %lu (requested %lu)",
effective_open_files, request_open_files);
}
else
{
sql_print_warning("Could not increase number of max_open_files to "
"more than %lu (request: %lu)",
effective_open_files, request_open_files);
}
}
open_files_limit= effective_open_files;
if (requested_open_files)
*requested_open_files= min<ulong>(effective_open_files, request_open_files);
}
这段源码很容易看懂，注释很详细，官方文档的解释与源码也很相符。按照三种算法，算出三个limit值，最后取其中最大的一个去设定这个变量。通过源码我们也可以看到open_files_limit，table_open_cache,max_connections这几个参数是密切相关的。
open_files_limit这个参数应该在my.cnf中[mysqld_safe]中设定，因为mysqld_safe脚本读到这个变量会尝试执行ulimit -n 改变针对当前环境的FD limits. 然后在把这个变量传给mysqld,因此需要用root启动mysqld_safe，否则可能会无法修改成功（非root用户所使用的值不能超过hard limit）. 如果只是在[mysqld]中指定这个变量，可能会受限于系统对默认的设置而无法生效。
[mysqld_safe]
core-file-size=10485760
open-files-limit=10000
启动后可以看到，这个变量作为参数传递给了mysqld
root 23344 1 0 11:16 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/dbfiles/mysql_home/data --socket=/dbfiles/mysql_home/data/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 24093 23344 0 11:29 pts/1 00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/dbfiles/mysql_home/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/dbfiles/mysql_home/data/mysqld.err --open-files-limit=10000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/dbfiles/mysql_home/data/mysql.sock --port=3306
查看该进程的FD limits. 发现已改变。
[root@stg-pxc-test01]/dbfiles:cat /proc/24093/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 10485760 unlimited bytes
Max core file size 5368709120 5368709120 bytes
Max resident set unlimited unlimited bytes
Max processes 31403 31403 processes
Max open files 10000 10000 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 31403 31403 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
我们再看下mysqld_safe的shell片段
if test -w / -o "$USER" = "root"
then
if test "$user" != "root" -o $SET_USER = 1
then
USER_OPTION="--user=$user"
fi
# Change the err log to the right user, if it is in use
if [ $want_syslog -eq 0 ]; then
touch "$err_log"
chown $user "$err_log"
fi
if test -n "$open_files"
then
ulimit -n $open_files
fi
fi
if test -n "$open_files"
then
append_arg_to_args "--open-files-limit=$open_files"
fi
由这段shell片段，我们可以看到，如果/目录不可写，或者用户不是root,设置文件描述符限制的操作就跳过去了。只有满足上面的判断条件，才会设置，并在设置完FD limits后，又把这个参数值通过--open-files-limit传给了mysqld.
CentOS 7使用了systemd管理进程，安装mysql时mysqld_safe不再被安装。若要配置open_file_limit可通过systemctl edit mysqld， 编辑service模块(或手工创建文件override.conf于目录/etc/systemd/system/mysqld.service.d下)。
[Service]
LimitNOFILE=max_open_files
PIDFile=/path/to/pid/file
Nice=nice_level
LimitCore=core_file_limit
Environment="LD_PRELOAD=/path/to/malloc/library"
Environment="TZ=time_zone_setting"
如此便可与mysqld_safe一样修改open_files_limit了.
